package com.xueyi.aixihu.mapper;

import com.xueyi.aixihu.entity.po.AxhFinance;
import com.xueyi.aixihu.entity.po.AxhFinanceReportCommunity;
import com.xueyi.aixihu.entity.po.AxhFinanceReportVillage;
import com.xueyi.aixihu.entity.po.DtNewsSort;
import com.xueyi.aixihu.entity.vo.*;
import com.xueyi.aixihu.reqeust.FinancePublicListReq;
import com.xueyi.aixihu.reqeust.FinanceReportReq;
import com.xueyi.aixihu.reqeust.FinanceReq;
import com.xueyi.aixihu.reqeust.base.PageLimitReq;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 描述 报表Mapper
 *
 * @auther 俞烨
 * @date 2022-08-17 10
 */
public interface FinanceMapper {


    /**
     * 批量添加报表的信息
     *
     * @param financeReportCommunities
     */
    @Insert("<script>" +
            "  INSERT INTO axh_finance_report_community (pid , line_type , line_name , head_money , end_money ,finance_id)" +
            "   VALUES" +
            " <foreach collection='list' item='exportItem' separator=','> " +
            "    (#{exportItem.item.pid} , #{exportItem.item.lineType} , #{exportItem.item.lineName} , #{exportItem.item.headMoney} ," +
            " #{exportItem.item.endMoney} , #{exportItem.item.financeId}) " +
            " </foreach>" +
            "</script>")
    @Options(useGeneratedKeys = true, keyProperty = "item.id")
    void batchSaveFinance(@Param("list") List<ExportCommunityVo> financeReportCommunities);

    /**
     * 查询用户管理的小区信息
     *
     * @param id
     * @return
     */
    @Select("SELECT * FROM dt_news_sort WHERE id IN (SELECT manage_area_id FROM axh_user_manage_area WHERE user_id = #{id}) AND pid != 1 ORDER BY create_time DESC " +
            " LIMIT 1 ")
    DtNewsSort findUserManageAreaLimit1(Integer id);

    /**
     * 通过状态查询财务审核的数量信息
     *
     * @param financeReq       网页的req
     * @param userManageAreaId 只能看哪些小区
     * @param waitStatus       代办
     * @param turnDownStatus   驳回
     * @param otherStatus      其他
     * @return
     */
    @Select("<script>" +
            " SELECT " +
            "   COUNT(IF(current_status IN (${waitStatus}) AND (restart_audit IS NULL OR restart_audit = 0) ,id , null)) waitSize , " +
            "   COUNT(IF(current_status IN (${turnDownStatus}) AND (restart_audit IS NULL OR restart_audit = 0) ,id , null)) turnDownSize , " +
            "   COUNT(IF(current_status IN (${otherStatus}) AND (restart_audit IS NULL OR restart_audit = 0) ,id , null)) otherSize , " +
            "   COUNT(IF(restart_audit = 1 ,id , null)) twoAuditSize " +
            " FROM axh_finance WHERE is_valid = 1 AND from_to_area_id IN (${userManageAreaId}) " +
            " <if test='req.financeTitle != null'>" +
            "    AND finance_title LIKE '%${req.financeTitle}%'   " +
            " </if> " +
            " <if test='req.month != null'>" +
            "    AND create_time &gt;= '${req.month}-01 00:00:00' AND create_time &lt;= '${req.month}-31 23:59:59'  " +
            " </if> " +
            " <if test='req.oneType != null'>" +
            "    AND  from_to_area_id IN (SELECT id FROM dt_news_sort WHERE pid = #{req.oneType}) " +
            " </if> " +
            " <if test='req.twoType != null'>" +
            "    AND  from_to_area_id = #{req.twoType} " +
            " </if> " +
            " <if test='req.areaType != null'>" +
            "    AND  area_type = #{req.areaType} " +
            " </if> " +
            "</script>")
    FinanceAuditSizeVo findFinanceAuditSize(@Param("req") FinanceReq financeReq,
                                            @Param("userManageAreaId") String userManageAreaId,
                                            @Param("waitStatus") String waitStatus,
                                            @Param("turnDownStatus") String turnDownStatus,
                                            @Param("otherStatus") String otherStatus);

    /**
     * 通过状态查询财务审核的数量信息
     *
     * @param financeReq       网页的req
     * @param userManageAreaId 只能看哪些小区
     * @param waitStatus       代办
     * @param turnDownStatus   驳回
     * @param otherStatus      其他
     * @return
     */
    @Select("<script>" +
            " SELECT " +
            "   SUM(borrow_total_money) borrowTotal , SUM(loan_total_money) loanTotal " +
            " FROM axh_finance WHERE is_valid = 1 AND from_to_area_id IN (${userManageAreaId}) " +
            " <if test='req.financeTitle != null'>" +
            "    AND finance_title LIKE '%${req.financeTitle}%'   " +
            " </if> " +
            " <if test='req.month != null'>" +
            "    AND create_time &gt;= '${req.month}-01 00:00:00' AND create_time &lt;= '${req.month}-31 23:59:59'  " +
            " </if> " +
            " <if test='req.oneType != null'>" +
            "    AND  from_to_area_id IN (SELECT id FROM dt_news_sort WHERE pid = #{req.oneType}) " +
            " </if> " +
            " <if test='req.twoType != null'>" +
            "    AND  from_to_area_id = #{req.twoType} " +
            " </if> " +
            " <if test='req.areaType != null'>" +
            "    AND  area_type = #{req.areaType} " +
            " </if> " +
            " AND current_status IN (${waitStatus} , ${turnDownStatus}) " +
            "</script>")
    AuditTotalVo findAuditTotal(@Param("req") FinanceReq financeReq,
                                @Param("userManageAreaId") String userManageAreaId,
                                @Param("waitStatus") String waitStatus,
                                @Param("turnDownStatus") String turnDownStatus,
                                @Param("otherStatus") String otherStatus);


    /**
     * 通过状态查询财务审核的数量信息
     *
     * @param financeReq       网页的req
     * @param userManageAreaId 只能看哪些小区
     * @return
     */
    @Select("<script>" +
            "  SELECT COUNT(id) FROM axh_finance WHERE is_valid = 1 AND from_to_area_id IN (${userManageAreaId}) " +
            " <if test='req.financeTitle != null'>" +
            "    AND finance_title LIKE '%${req.financeTitle}%'   " +
            " </if> " +
            " <if test='req.month != null'>" +
            "    AND create_time &gt;= '${req.month}-01 00:00:00' AND create_time &lt;= '${req.month}-31 23:59:59'  " +
            " </if> " +
            " <if test='req.oneType != null'>" +
            "    AND  from_to_area_id IN (SELECT id FROM dt_news_sort WHERE pid = #{req.oneType}) " +
            " </if> " +
            " <if test='req.twoType != null'>" +
            "    AND  from_to_area_id IN (${req.twoType}) " +
            " </if>" +
            " <if test='currentStatus != null'>" +
            "    AND  current_status IN (${currentStatus}) " +
            " </if>" +
            " <if test='req.fb2 != null'>" +
            "     AND fb_reject_detail IS NOT NULL " +
            " </if>" +
            "</script>")
    Integer findFinanceAuditSizeByStatus(@Param("req") FinanceReq financeReq,
                                         @Param("userManageAreaId") String userManageAreaId,
                                         @Param("currentStatus") String currentStatus);

    /**
     * 通过状态查询财务审核的集合
     *
     * @param financeReq       网页的req
     * @param userManageAreaId 只能看哪些小区
     * @return
     */
    @Select("<script>" +
            " SELECT a1.* , dtUsers.avatar photo , dns.`name` areaName , a1.area_type  FROM ( " +
            "  SELECT * FROM axh_finance WHERE is_valid = 1 AND from_to_area_id IN (${userManageAreaId}) " +
            " <if test='req.financeTitle != null'>" +
            "    AND finance_title LIKE '%${req.financeTitle}%'   " +
            " </if> " +
            " <if test='req.month != null and req.isYear'>" +
            "    AND create_time &gt;= '${req.month}-01-01 00:00:00' AND create_time &lt;= '${req.month}-12-31 23:59:59'  " +
            " </if> " +
            " <if test='req.month != null and !req.isYear'>" +
            "    AND create_time &gt;= '${req.month}-01 00:00:00' AND create_time &lt;= '${req.month}-31 23:59:59'  " +
            " </if> " +
            " <if test='req.fb != null and req.fb == 1'>" +
            "    AND restart_audit = 1 " +
            " </if> " +
            " <if test='req.fb2 != null and req.fb2 == 1'>" +
            "    AND fb_reject_detail IS NOT NULL " +
            " </if> " +
            " <if test='req.oneType != null'>" +
            "    AND  from_to_area_id IN (SELECT id FROM dt_news_sort WHERE pid = #{req.oneType}) " +
            " </if> " +
            " <if test='req.twoType != null'>" +
            "    AND  from_to_area_id IN(${req.twoType}) " +
            " </if>" +
            " <if test='req.isPublic != null'>" +
            "    AND  is_public = #{req.isPublic} " +
            " </if>" +
            " <if test='req.isPublic != null'>" +
            "    AND  is_public = #{req.isPublic} " +
            " </if>" +
            " <if test='currentStatus != null'>" +
            " AND  current_status IN (${currentStatus}) " +
            " <if test='req.fb2 != null'>" +
            "   AND (fb_reject_detail IS NOT NULL) " +
            " </if>" +
            " <if test='req.fb2 == null'>" +
            "   AND (fb_reject_detail IS NULL) " +
            " </if>" +
            " <if test='req.fb != null'>" +
            "   AND (restart_audit IS NULL OR restart_audit = 0) " +
            " </if>" +
            "   " +
            " </if>" +
            " ORDER BY create_time DESC LIMIT #{req.startIndex} , #{req.limit} " +
            " ) a1 LEFT JOIN dt_users dtUsers ON a1.create_user_id = dtUsers.id " +
            " LEFT JOIN dt_news_sort dns ON a1.from_to_area_id = dns.id " +
            " ORDER BY a1.create_time DESC " +
            "</script>")
    List<FinanceAuditVo> findFinanceAuditList(@Param("req") FinanceReq financeReq,
                                              @Param("userManageAreaId") String userManageAreaId,
                                              @Param("currentStatus") String currentStatus);

    /***
     * 查询日志
     * @param id
     * @return
     */
    @Select("SELECT a1.* , du.username username , af.area_type  FROM (" +
            " SELECT * FROM axh_finance_log WHERE finance_id = #{id} AND current_finance_status = #{type} and log_type = 1 ORDER BY create_time DESC LIMIT 1" +
            " ) a1 LEFT JOIN dt_users du ON a1.command_user_id = du.id " +
            " LEFT JOIN axh_finance af ON a1.finance_id = af.id ")
    AxhAuditFinanceLogVo findFinanceLog(@Param("id") Integer id, @Param("type") Integer type);


    /***
     * 查询日志
     * @param id
     * @return
     */
    @Select("SELECT a1.* , du.username username , af.area_type  FROM (" +
            " SELECT * FROM axh_finance_log WHERE finance_id = #{id} AND current_finance_status = #{type} AND log_content LIKE '%公开状态%' ORDER BY create_time DESC LIMIT 1" +
            " ) a1 LEFT JOIN dt_users du ON a1.command_user_id = du.id " +
            " LEFT JOIN axh_finance af ON a1.finance_id = af.id ")
    AxhAuditFinanceLogVo findFinancePublicLog(@Param("id") Integer id, @Param("type") Integer type);

    /**
     * 查询报表
     *
     * @param id
     * @return
     */
    @Select("SELECT a1.* , dns.`name` areaName FROM ( " +
            " SELECT * FROM axh_finance WHERE id = #{id} AND is_valid = 1" +
            " ) a1 LEFT JOIN dt_news_sort dns ON a1.from_to_area_id = dns.id")
    AxhFinance findFinance(Integer id);

    /**
     * 查询财务报销Vo
     *
     * @param financeExpenseReq
     * @param userManageAreaId
     * @return
     */
    @Select("<script>" +
            " SELECT " +
            " COUNT(id) totalSize , " +
            " COUNT(IF(current_status = 4 AND (fb_reject_detail IS NULL) , id , null)) passSize , " +
            " COUNT(IF(current_status IN (1,2,3) AND (fb_reject_detail IS NULL) , id , null)) waitSize , " +
            " COUNT(IF(current_status IN (-1,-2,-3) AND (fb_reject_detail IS NULL) , id , null)) turnDownSize , " +
            " COUNT(IF(current_status = 0 AND (fb_reject_detail IS NULL) , id , null)) nodeTextSize , " +
            " COUNT(IF((fb_reject_detail IS NOT NULL) , id , null)) twoAuditTurn " +
            " FROM axh_finance WHERE is_valid = 1 AND from_to_area_id IN (${userManageAreaId}) " +
            " <if test='req.financeTitle != null'>" +
            "   AND finance_title LIKE '%${req.financeTitle}%' " +
            " </if> " +
            " <if test='req.month != null'>" +
            "    AND create_time &gt;= '${req.month}-01 00:00:00' AND create_time &lt;= '${req.month}-31 23:59:59'  " +
            " </if> " +
            "</script>")
    FinanceExpenseSizeVo findFinanceExpenseSizeVo(@Param("req") FinanceExpenseReq financeExpenseReq,
                                                  @Param("userManageAreaId") String userManageAreaId);


    /**
     * 修改报表的公开状态
     *
     * @param id
     * @param publicStatus
     * @param userManageAreaId
     */
    @Update("UPDATE axh_finance SET is_public = #{publicStatus} WHERE id = #{id} AND from_to_area_id IN(${userManageAreaId})")
    void updateFinancePublicStatus(@Param("id") Integer id,
                                   @Param("publicStatus") Integer publicStatus,
                                   @Param("userManageAreaId") String userManageAreaId);

    /**
     * 通过id查询自己管辖的报表
     *
     * @param id
     * @param userManageAreaId
     * @return
     */
    @Select("SELECT * FROM axh_finance WHERE id = #{id} AND from_to_area_id IN (${userManageAreaId})")
    AxhFinance findFinanceAndManageAreaId(@Param("id") Integer id,
                                          @Param("userManageAreaId") String userManageAreaId);

    /**
     * 修改报表的状态
     *
     * @param id
     * @param userManageAreaId
     * @param status
     */
    @Update("UPDATE axh_finance SET current_status = #{status} , last_update_time = now() WHERE id = #{id} AND from_to_area_id IN (${userManageAreaId})")
    void updateFinanceStatus(@Param("id") Integer id,
                             @Param("userManageAreaId") String userManageAreaId,
                             @Param("status") int status);

    /**
     * 查询这个村这个角色的用户信息
     *
     * @param roles
     * @param addId
     * @return
     */
    @Select("SELECT * FROM dt_users WHERE id IN (SELECT user_id FROM axh_user_manage_area WHERE manage_area_id = #{addId})" +
            " AND id IN (SELECT uid FROM dt_auth_group_access WHERE group_id IN (${roles})) " +
            " AND is_valid = 1 ")
    List<DtUsersVo> findDtUserByRoles(@Param("roles") String roles,
                                      @Param("addId") Integer addId);

    /**
     * 批量添加报表的信息
     *
     * @param exportCommunityVos
     */
    @Insert("<script>" +
            "  INSERT INTO axh_finance_report_village (finance_number , line_name , head_money_type , head_money , current_borrow , current_loan , end_money_type , end_money , pid ,finance_id)" +
            "   VALUES" +
            " <foreach collection='list' item='exportItem' separator=','> " +
            "    (#{exportItem.item.financeNumber} , #{exportItem.item.lineName} , #{exportItem.item.headMoneyType} , #{exportItem.item.headMoney} ," +
            " #{exportItem.item.currentBorrow} , #{exportItem.item.currentLoan}  #{exportItem.item.endMoneyType} , " +
            "  #{exportItem.item.endMoney} , #{exportItem.item.pid} , #{exportItem.item.financeId}) " +
            " </foreach>" +
            "</script>")
    @Options(useGeneratedKeys = true, keyProperty = "item.id")
    void batchSaveFinanceVillage(@Param("list") List<ExportCommunityVo> exportCommunityVos);

    /**
     * 通过id和父id查询社区的报表详情
     *
     * @param id
     * @param pid
     * @return
     */
    @Select("<script>" +
            "SELECT * FROM axh_finance_report_community WHERE finance_id = #{id} AND pid = #{pid} AND be_long_id IS NULL " +
            " <if test='!look'>" +
            "   AND (fb_status != 3   OR  fb_status IS NULL) AND is_valid IN ( 1 , 2 )   " +
            " </if>" +
            " <if test='look'>" +
            "   AND is_valid = 1  " +
            " </if>" +
            "</script>")
    List<AxhFinanceReportCommunity> findFinanceDetailCommunities(@Param("id") Integer id, @Param("pid") Integer pid,
                                                                 @Param("look") boolean look);

    /**
     * 通过id和父id查询村的报表详情
     *
     * @param id
     * @param pid
     * @return
     */
    @Select("<script>" +
            "SELECT * FROM axh_finance_report_village WHERE finance_id = #{id} AND pid = #{pid} AND be_long_id IS NULL " +
            " <if test='!look'>" +
            "   AND (fb_status != 3   OR  fb_status IS NULL) AND is_valid IN ( 1 , 2 )   " +
            " </if>" +
            " <if test='look'>" +
            "   AND is_valid = 1  " +
            " </if>" +
            "</script>")
    List<AxhFinanceReportVillage> findFinanceDetailVillage(@Param("id") Integer id, @Param("pid") Integer pid,
                                                           @Param("look") boolean look);

    /**
     * 删除或者恢复报表
     *
     * @param id
     * @param status
     */
    @Update("UPDATE axh_finance SET is_valid = #{status} WHERE id = #{id}")
    void delOrRecoverReport(Integer id, Integer status);

    /**
     * 修改附件
     *
     * @param id
     * @param accessory
     * @param tableName
     */
    @Update("UPDATE ${tableName} SET accessory = #{accessory} , last_update_time = now() WHERE id = #{id}")
    void uploadFinanceAccessory(@Param("id") Integer id, @Param("accessory") String accessory, @Param("tableName") String tableName);

    /**
     * 删除报表的某一行数据
     *
     * @param lineId
     * @param tableName
     */
    @Update("UPDATE ${tableName} SET is_valid = 0 WHERE id = #{lineId}")
    void deleteFinanceLine(@Param("lineId") Integer lineId,
                           @Param("tableName") String tableName);

    /**
     * 查询财务数据中心总计信息
     *
     * @param req
     * @param manageAreaId
     * @return
     */
    @Select("SELECT " +
            " COUNT(IF(area_type = 2 AND current_status != 0 , id , null)) villageSize , " +
            " COUNT(IF(area_type = 1 AND current_status != 0 , id , null)) communitySize , " +
            " COUNT(IF(current_status IN (1,2,3,-1,-2,-3)  , id , null)) waitSize , " +
            " COUNT(IF(current_status = 4 AND is_public = 1 , id , null)) publicSize " +
            " FROM axh_finance WHERE from_to_area_id IN (${manageAreaId}) AND is_valid = 1 ")
    FinanceReportSizeVo findFinanceReportSizeVo(@Param("req") FinanceReportReq req,
                                                @Param("manageAreaId") String manageAreaId,
                                                @Param("waitStatus") String waitStatus);

    /**
     * 查看数据看板的集合
     *
     * @param req
     * @return
     */
    @Select("<script> " +
            " SELECT dns.id , dns.name areaName , af.is_public , af.current_status FROM ( " +
            " SELECT * FROM dt_news_sort WHERE id IN (${manageAreaId}) AND is_valid = 1 AND pid = #{req.type}  " +
            " ) dns LEFT JOIN axh_finance af ON dns.id = af.from_to_area_id AND af.is_valid = 1 AND af.current_status != 0 " +
            " <if test='req.monthData != null'> " +
            "    AND af.create_time &gt;= '${req.monthData}-01 00:00:00' AND af.create_time &lt;= '${lastTime}'  " +
            " </if>" +
            "</script>")
    List<DataBlackListVo.BlackList> findFinanceDataBlackList(@Param("req") FinanceReportReq req,
                                                             @Param("manageAreaId") String manageAreaId ,
                                                             @Param("lastTime") String lastTime);

    /**
     * 查询日志的信息
     *
     * @param req
     * @return
     */
    @Select("" +
            " SELECT afl.* , du.username FROM ( " +
            "  SELECT * FROM axh_finance WHERE from_to_area_id IN (${manageIds}) " +
            " ) a1 LEFT JOIN axh_finance_log afl ON a1.id = afl.finance_id" +
            " LEFT JOIN dt_users du ON afl.command_user_id = du.id" +
            " ORDER BY afl.create_time DESC LIMIT #{req.startIndex} , #{req.limit} ")
    List<AxhAuditFinanceLogVo> findFinanceLogList(@Param("req") FinanceReportReq req, @Param("manageIds") String manageIds);

    /**
     * 查询单人的日志的信息
     *
     * @param id
     * @return
     */
    @Select(" select * FROM axh_finance_log " +
            " where command_user_id = #{id} and is_valid = 1 " +
            " ORDER BY create_time DESC LIMIT #{req.startIndex} , #{req.limit} ")
    List<AxhAuditFinanceLogVo> findOneFinanceLogList(@Param("req") PageLimitReq req, @Param("id") Integer id);

    /**
     * 查询单人的日志的信息条数
     *
     * @param id
     * @return
     */
    @Select(" select count(id) FROM axh_finance_log " +
            " where command_user_id = #{id} and is_valid = 1 ")
    Integer findOneFinanceLogListCount(@Param("req") PageLimitReq req, @Param("id") Integer id);

    /**
     * 查询分析数据的报表信息
     *
     * @param req
     * @param manageAreaId
     * @return
     */
    @Select("<script>" +
            "  SELECT SUM(borrow_total_money) borrow , SUM(loan_total_money) loan FROM axh_finance WHERE from_to_area_id IN (${manageAreaId}) " +
            " AND create_time &gt;= '${req.year}-${req.monthData}-01 00:00:00' AND create_time &lt;= '${req.year}-${req.monthData}-31 23:59:59' " +
            " AND area_type = #{req.type} AND current_status = 4 AND is_valid = 1 " +
            " <if test='req.addressId != null'>" +
            "   AND from_to_area_id = #{req.addressId} " +
            " </if> " +
            " </script> ")
    FinanceFxEchartsVo financeFxEchartsVo(@Param("req") FinanceReportReq req,
                                          @Param("manageAreaId") String manageAreaId);

    /**
     * 查询村和社区的数量
     *
     * @return
     */
    @Select("SELECT COUNT(id) FROM dt_news_sort WHERE pid = #{type} AND pid != 1 AND id IN (${userManageAreaId})")
    Integer findVillageAndComSize(@Param("type") Integer type, @Param("userManageAreaId") String userManageAreaId);


    /**
     * 查询村和社区公开和总共的数量
     *
     * @param type
     * @return
     */
    @Select("SELECT COUNT(IF(current_status != 0 , id , null))                          totalSize,\n" +
            "       COUNT(IF(is_public = 1, id, null)) publicSize\n" +
            "FROM axh_finance\n" +
            "where (from_to_area_id IN (SELECT id FROM dt_news_sort WHERE pid = #{type}) AND from_to_area_id " +
            " IN (${userManageAreaId}))\n" +
            "  AND is_valid = 1")
    IndexAccountDetail.FinanceSize findTotalAndPublicSize(@Param("type") int type,
                                                          @Param("userManageAreaId") String userManageAreaId);

    /**
     * 新增请求的数量
     *
     * @param reqSize
     */
    @Insert("INSERT INTO axh_req_size (req_size) VALUES (#{reqSize})")
    void saveReqSize(int reqSize);

    /**
     * 查询每个月的访问量
     *
     * @param month
     * @return
     */
    @Select("SELECT req_size as `value`, DATE_FORMAT(create_time , '%d') as label  FROM axh_req_size WHERE create_time >= '${month}-01 00:00:00' AND create_time <= '${month}-31 23:59:59'")
    List<LineReportVo> findReqSizeLineReport(@Param("month") String month);

    /**
     * 查询附件信息
     *
     * @param lineId
     * @param tableName
     * @return
     */
    @Select("SELECT accessory FROM ${tableName} WHERE id = #{lineId}")
    String findFinanceAccessory(Integer lineId, String tableName);

    /**
     * 查询公开的财务列表
     *
     * @param financePublicListReq
     * @return
     */
    @Select("<script>" +
            " SELECT id , finance_title , create_time , area_type FROM axh_finance WHERE is_public = 1 AND is_valid = 1 " +
            " <if test='month != null'>" +
            "    AND create_time &gt;= '${month}-01 00:00:00' AND create_time &lt;= '${month}-31 23:59:59'  " +
            " </if> " +
            " <if test='keyWord != null'>" +
            "    AND finance_title LIKE '%${keyWord}%'  " +
            " </if> " +
            " <if test='areaType != null'>" +
            "    AND area_type = #{areaType} " +
            " </if> " +
            " <if test='areaIds != null'>" +
            "    AND from_to_area_id IN (${areaIds})  " +
            " </if> " +
            " ORDER BY create_time DESC LIMIT #{startIndex} , #{limit} " +
            "</script>")
    List<FinanceAuditVo> findPublicFinanceList(FinancePublicListReq financePublicListReq);

    /**
     * 查询报表
     *
     * @param financeId
     * @return
     */
    @Select("SELECT * FROM axh_finance_log WHERE finance_id = #{financeId} and log_type = #{type} ORDER BY create_time DESC")
    List<AuditLogRecord> findAuditLogRecord(@Param("financeId") Integer financeId, @Param("type") Integer type);

    @Select("SELECT id , `name` areaName FROM dt_news_sort WHERE pid = #{type} AND is_valid = 1")
    List<DataBlackListVo.BlackList> findSort(String type);

    @Delete("DELETE FROM ${tableName} WHERE be_long_id = #{id} ")
    void deleteFb(@Param("id") Integer id, @Param("tableName") String tableName);

    @Select("SELECT * FROM ${tableName} WHERE be_long_id = #{id}")
    AxhFinanceReportCommunity findFbTemp(@Param("id") Integer id, @Param("tableName") String tableName);

    @Select("SELECT * FROM ${tableName} WHERE be_long_id = #{id}")
    AxhFinanceReportVillage findFbTemp2(@Param("id") Integer id, @Param("tableName") String tableName);

    @Update("UPDATE ${tableName} SET fb_status = #{status} WHERE id = #{id}")
    void updateFbStatus(@Param("id") Integer id, @Param("tableName") String tableName, @Param("status") Integer status);

    @Update("UPDATE axh_finance SET fb_status = #{status} WHERE id = #{financeId}")
    void updateFinanceFb(@Param("financeId") Integer financeId,
                         @Param("status") Integer status);

    @Update("UPDATE axh_finance SET fb_status = #{status} , fb_reject_detail = NULL WHERE id = #{financeId}")
    void updateFinanceAndRejectMsgFb(@Param("financeId") Integer financeId,
                                     @Param("status") Integer status);

    @Delete("DELETE FROM ${tableName} WHERE (be_long_id IS NOT NULL OR fb_status = 3) AND finance_id = #{id}")
    void deleteFinanceFb(@Param("id") Integer financeId, @Param("tableName") String tableName);

    @Update("UPDATE axh_finance SET restart_audit = #{audit} , fb_reject_detail = #{remark} WHERE id = #{id}")
    void rejectFbFinance(@Param("id") Integer id, @Param("remark") String remark,
                         @Param("audit") Integer audit);

    @Update("UPDATE ${tableName} SET fb_status = NULL WHERE finance_id = #{id}")
    void updateFinanceLineFb(@Param("id") Integer financeId, @Param("tableName") String tableName);

    @Select("DELETE FROM ${tableName} WHERE finance_id = #{id} AND fb_status = 2")
    void findOldId(@Param("id") Integer financeId, @Param("tableName") String tableName);

    @Update("UPDATE ${tableName} SET is_valid = 0 WHERE finance_id = #{id} AND fb_status = 2")
    void delFinanceByStatus(@Param("id") Integer financeId, @Param("tableName") String tableName);

    @Update("UPDATE ${tableName} SET id = be_long_id , be_long_id = NULL , fb_status = NULL WHERE finance_id = #{id} AND be_long_id IS NOT NULL ")
    void useFb(@Param("id") Integer financeId, @Param("tableName") String tableName);

    @Update("UPDATE ${tableName} SET accessory = #{accessory} , last_update_time = now() WHERE id = #{id}")
    void uploadFinanceAccessory2(@Param("id") Integer lineId, @Param("accessory") String accessory, @Param("tableName") String tableName);

    @Select("SELECT * FROM ${tableName} WHERE id = #{id}")
    AxhFinanceReportCommunity findLine(@Param("id") Integer lineId, @Param("tableName") String tableName);

    @Select("SELECT * FROM ${tableName} WHERE id = #{id}")
    AxhFinanceReportVillage findLine2(@Param("id") Integer lineId, @Param("tableName") String tableName);

    @Update("UPDATE axh_finance SET fb_reject_detail = NULL WHERE id = #{financeId}")
    void clearTwoAuditMsg(Integer financeId);

    @Select("SELECT id FROM ${tableName} WHERE be_long_id = #{id} LIMIT 1 ")
    Integer findBeLongIdByOldId(@Param("id") Integer lineId, @Param("tableName") String tableName);

    @Update("UPDATE ${tableName} SET id = #{beLongFbId} , be_long_id = #{oId} WHERE id = #{oId}")
    void updateParentId(@Param("oId") Integer oId,
                        @Param("tableName") String tableName,
                        @Param("beLongFbId") Integer beLongFbId);


    @Update("DELETE FROM ${tableName} WHERE id = #{lineId}")
    void deleteFbFinanceLine(@Param("lineId") Integer lineId,
                             @Param("tableName") String tableName);

    @Update("UPDATE  ${tableName} SET is_valid = 1 WHERE finance_id = #{id} AND is_valid = 2")
    void updateIsValid2(@Param("id") Integer id,
                        @Param("tableName") String tableName);

    @Update("UPDATE ${tableName} SET be_long_id = NULL , fb_status = NULL WHERE finance_id = #{id}")
    void clearFbStatus(@Param("id") Integer id,
                       @Param("tableName") String tableName);

    @Select("SELECT a1.finance_title , a1.id , du.wx_open_id FROM ( " +
            " SELECT * FROM axh_finance WHERE current_status = 4 AND is_public = 0 AND from_to_area_id IN (${areaIds}) " +
            " ) a1 LEFT JOIN dt_users du ON a1.create_user_id = du.id ")
    List<NotifyVo> findNotifyList(@Param("areaIds") String areaIds);

}
